﻿using EF6PowerToolDemo.Models;
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace EF6PowerToolDemo
{
    /// <summary>
    /// ASP.NET MVC入门到精通——Entity Framework增删改查
    /// http://www.cnblogs.com/jiekzou/p/4731655.html
    /// </summary>
    public class Class0
    {
        public static void Test()
        {
            //Delete();

            //Add();

            //Edit();
            Edit3();

            Console.WriteLine("\nPress <enter> to continue...");
            Console.ReadLine();
        }

        #region 1. 添加

        private static int Add()
        {
            using (var db = new NorthwindContext())
            {
                var orders = db.Orders.ToList();

                Customer _Customers = new Customer
                {
                    CustomerID = "zhang",
                    Address = "洛阳西街",
                    City = "洛阳",
                    Phone = "1314520",
                    CompanyName = "微软",
                    ContactName = "李四"
                };

                //方法一
                db.Customers.Add(_Customers);

                return db.SaveChanges();
            }
        }

        private static int Add1()
        {
            using (var db = new NorthwindContext())
            {
                var orders = db.Orders.ToList();

                Customer _Customers = new Customer
                {
                    CustomerID = "zhang",
                    Address = "洛阳西街",
                    City = "洛阳",
                    Phone = "1314520",
                    CompanyName = "微软",
                    ContactName = "李四"
                };

                //方法二
                DbEntityEntry entry = db.Entry(_Customers);
                entry.State = EntityState.Added;

                return db.SaveChanges();
            }
        }

        #endregion 1. 添加

        #region 2. 查询

        // 2.1 简单查询 延迟加载
        private static void QueryDelay_01()
        {
            using (var db = new NorthwindContext())
            {
                DbQuery<Customer> dbQuery = db.Customers.Where(u => u.ContactName == "张学友").OrderBy(u => u.ContactName).Take(2) as DbQuery<Customer>;

                //获得 延迟查询对象后，调用对象的 获取第一个数据方法，此时，【就会根据之前的条件】，生成sql语句，查询数据库了~~！
                Customer usr01 = dbQuery.FirstOrDefault();// ToList()......
                Console.WriteLine(usr01.ContactName);
            }
        }

        //2.1.2【延迟加载】- 针对于 外键实体 的延迟(按需加载)！
        // 本质原因之二：对于外键属性而言，EF会在用到这个外键属性的时候才去查询 对应的 表。
        private static void QueryDelay_02()
        {
            using (var db = new NorthwindContext())
            {
                IQueryable<Order> _Orders = db.Orders.Where(a => a.CustomerID == "TOMSP");//真实返回的 DbQuery 对象，以接口方式返回

                //a.此时只查询了 地址表
                Order order = _Orders.FirstOrDefault();

                //b.当访问 地址对象 里的 外键实体时，EF会查询 地址对应 的用户表;查询到之后，将数据 装入 这个外键实体
                Console.WriteLine(order.Customer.ContactName);

                //c.【延迟加载】按需加载 的缺点：每次调用外键实体时，都会去查询数据库（EF有小优化：相同的外键实体只查一次）
                IQueryable<Order> orderList = db.Orders;
                foreach (Order o in orderList)
                {
                    Console.WriteLine(o.OrderID + ":ContactName=" + o.Customer.ContactName);
                }
            }
        }

        #endregion 2. 查询

        #region 3. 修改

        /// <summary>
        /// 3.0 官方推荐的修改方式（先查询，再修改）
        /// </summary>
        private static void Edit()
        {
            using (var db = new NorthwindContext())
            {
                //1.查询出要修改的对象 -- 注意：此时返回的是一个Customers类的代理类对象（包装类对象）
                var customer = db.Customers.Where(u => u.CustomerID == "zhang").FirstOrDefault();
                Console.WriteLine("修改前：" + customer.ContactName);

                //2.修改内容 -- 注意：此时其实操作的是代理类对象的属性，这些属性，会将值设置给内部的Customers对象对应的属性，同时标记此属性为已修改状态
                customer.ContactName = "刘德华";

                //3.重新保存到数据库 -- 注意：此时ef上下文，会检查容器内部所有的对象，找到标记为修改的对象，然后找到标记为修改的对象属性，生成对应的update语句执行！
                db.SaveChanges();
                Console.WriteLine("修改成功：");

                Console.WriteLine(customer.ContactName);
            }
        }

        /// <summary>
        /// 3.1 自己优化的修改方式（创建对象，直接修改）
        /// </summary>
        static void Edit2()
        {
            using (var db = new NorthwindContext())
            {
                //1.创建一个要修改的对象
                Customer _Customers = new Customer()
                {
                    CustomerID = "zhang",
                    //Address = "洛阳西街",
                    //City = "洛阳",
                    //Phone = "1314520",
                    CompanyName = "微软", //注意：CompayName属性为非空，必填，否则会报错
                    ContactName = "张学友"
                };
                //2.将对象加入EF容器,并获取当前实体对象的状态管理对象
                DbEntityEntry<Customer> entry = db.Entry<Customer>(_Customers);
                //3.设置该对象为被修改过
                entry.State = EntityState.Unchanged;
                //4.设置该对象的ContactName属性为修改状态，同时entry.State被修改为 Modified 状态
                entry.Property("ContactName").IsModified = true;

                //3.重新保存到数据库 -- ef 上下文会根据实体对象的状态 ，根据 entry.State =Modified的值生成对应的update sql语句
                int n = db.SaveChanges();
                Console.WriteLine("修改成功：");
                Console.WriteLine(_Customers.ContactName);
            }
        }

        /// <summary>
        /// 3.1 自己优化的修改方式（创建对象，直接修改）
        /// </summary>
        static void Edit3()
        {
            using (var db = new NorthwindContext())
            {
                //1.创建一个要修改的对象
                Customer _Customer = new Customer()
                {
                    CustomerID = "zhang",
                    CompanyName = "微软", //注意：CompayName属性为非空，必填，否则会报错
                };

                //2.附加到EF中,设置需要修改的属性
                var customer = db.Customers.Attach(_Customer);
                customer.ContactName = "张学友";

                //3.重新保存到数据库
                int n = db.SaveChanges();
                Console.WriteLine("修改成功：");
                Console.WriteLine(_Customer.ContactName);
            }
        }
        #endregion

        #region 4. 删除

        /// <summary>
        /// 4.0 删除
        /// </summary>
        private static void Delete()
        {
            using (var db = new NorthwindContext())
            {
                // 1.创建要删除的对象
                Customer u = new Customer() { CustomerID = "zhang" };

                // 2.附加到EF中
                db.Customers.Attach(u);
                 
                // 3.标记为删除 注意：此方法就是起到了标记当前对象为删除状态 ！
                db.Customers.Remove(u);

                // 4.执行删除sql
                db.SaveChanges();

                Console.WriteLine("删除成功~~~");
            }
        }

        /// <summary>
        /// 4.1 删除
        /// </summary>
        private static void Delete1()
        {
            using (var db = new NorthwindContext())
            {
                // 1.创建要删除的对象
                Customer u = new Customer() { CustomerID = "zhang" };

                // 2.可以使用Entry来附加和修改
                DbEntityEntry<Customer> entry = db.Entry<Customer>(u);
                entry.State = EntityState.Deleted;

                // 3.执行删除sql
                db.SaveChanges();
                Console.WriteLine("删除成功~~~");
            }
        }

        #endregion 4. 删除

        #region 5. 批处理

        /// <summary>
        /// 5.0 批处理 -- 上下文 SaveChanges 方法 的 好处！！！！
        /// </summary>
        private static void SaveBatched()
        {
            using (var db = new NorthwindContext())
            {
                // 5.1 新增数据
                Customer _Customers = new Customer
                {
                    CustomerID = "zou",
                    Address = "洛阳西街",
                    City = "洛阳",
                    Phone = "1314520",
                    CompanyName = "微软",
                    ContactName = "邹玉杰"
                };
                db.Customers.Add(_Customers);

                // 5.2 新增第二个数据
                Customer _Customers2 = new Customer
                {
                    CustomerID = "zhao",
                    Address = "洛阳西街",
                    City = "洛阳",
                    Phone = "1314520",
                    CompanyName = "微软",
                    ContactName = "赵匡胤"
                };
                db.Customers.Add(_Customers2);

                // 5.3 修改数据
                Customer usr = new Customer() { CustomerID = "zhao", ContactName = "赵牧" };
                DbEntityEntry<Customer> entry = db.Entry<Customer>(usr);
                entry.State = EntityState.Unchanged;
                entry.Property("ContactName").IsModified = true;

                // 5.4 删除数据
                Customer u = new Customer() { CustomerID = "zou" };
                db.Customers.Attach(u);
                db.Customers.Remove(u);

                db.SaveChanges();
                Console.WriteLine("批处理 完成~~~~~~~~~~~~！");
            }
        }

        /// <summary>
        /// 5.1 批处理 -- 一次新增 50条数据
        /// </summary>
        private static void BatcheAdd()
        {
            using (var db = new NorthwindContext())
            {
                for (int i = 0; i < 50; i++)
                {
                    Customer _Customers = new Customer
                    {
                        CustomerID = "zou" + i,
                        Address = "洛阳西街",
                        City = "洛阳",
                        Phone = "1314520",
                        CompanyName = "微软",
                        ContactName = "邹玉杰" + i
                    };
                    db.Customers.Add(_Customers);
                }
                db.SaveChanges();
            }
        }
        #endregion 5. 批处理
    }
}